package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;

import com.domain.Employees;

public class EmployeesDAO extends BaseDAO {
	public List<Employees> getAllEmployees() {
		List<Employees> e = new LinkedList<Employees>();
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = getConnection();
			Statement statement = conn.createStatement();
			String query = "SELECT * FROM employees";
			rs = statement.executeQuery(query);
			while (rs.next()) {
				String employeeId = rs.getString("employeeId");
				String username = rs.getString("username");
				String firstName = rs.getString("firstName");
				String lastName = rs.getString("lastName");
				String password = rs.getString("password");
				String extension = rs.getString("extension");
				String email = rs.getString("email");
				Employees employee = new Employees(employeeId, username,
						lastName, firstName, password, extension, email);
				e.add(employee);
			}
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return e;
	}

	public Employees getEmployeeById(String code) {
		Employees e = null;
		try {
			Connection conn = getConnection();
			String sql = "select * from employees where employeeId=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, code);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			if (!rs.wasNull()) {
				String employeeId = rs.getString("employeeId");
				String username = rs.getString("username");
				String lastName = rs.getString("lastName");
				String firstName = rs.getString("firstName");
				String password = rs.getString("password");
				String extension = rs.getString("extension");
				String email = rs.getString("email");
				e = new Employees(employeeId, username, lastName, firstName,
						password, extension, email);
			}
			conn.close();
		} catch (NamingException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		}
		return e;
	}

	public void deleteEmployees(Employees employees) {
		try {
			Connection conn = getConnection();
			String sql = "delete from employees where employeeId=? and username=? and lastName=? and firstName=? and password=? and extension=? and email=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, employees.getEmployeeId());
			stmt.setString(2, employees.getUsername());
			stmt.setString(3, employees.getLastName());
			stmt.setString(4, employees.getFirstName());
			stmt.setString(5, employees.getPassword());
			stmt.setString(6, employees.getExtension());
			stmt.setString(7, employees.getEmail());
			stmt.executeUpdate();
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int insert(Employees employees) {
		int result = 0;
		try {
			Connection conn = getConnection();
			String sql = "insert into employees values(?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, employees.getEmployeeId());
			stmt.setString(2, employees.getUsername());
			stmt.setString(3, employees.getFirstName());
			stmt.setString(4, employees.getLastName());
			stmt.setString(5, employees.getPassword());
			stmt.setString(6, employees.getExtension());
			stmt.setString(7, employees.getEmail());
			result = stmt.executeUpdate();
			conn.close();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public List<Employees> getUserByName(String input) {
		List<Employees> list = new LinkedList<Employees>();
		try {
			Connection conn = getConnection();
			String sql = "select * from employees where username=?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			stmt.setString(1, input);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				if (!rs.wasNull()) {
					String employeeId = rs.getString("employeeId");
					String username = rs.getString("username");
					String lastName = rs.getString("lastName");
					String firstName = rs.getString("firstName");
					String password = rs.getString("password");
					String extension = rs.getString("extension");
					String email = rs.getString("email");
					Employees e = new Employees(employeeId, username, lastName,
							firstName, password, extension, email);
					list.add(e);
				}
			}
			conn.close();
		} catch (NamingException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		} catch (SQLException ex) {
			// TODO Auto-generated catch block
			ex.printStackTrace();
		}
		return list;
	}

}
